#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "


delete  from hive.edu_dm.hkl_dm_check_wide_timecnt where 1=1;
insert into hive.edu_dm.hkl_dm_check_wide_timecnt
with tmp1 as (
select
t.*,
s.studying_student_count
from hive.edu_dws.hkl_dws_check_wide_timecnt t
left join hive.edu_dwd.hkl_dim_studying_student s
on t.class_id=s.class_id and t.dt=s.studying_date
where s.class_id is not null or s.studying_date is not null
),
tmp2 as (select
'2024-08-24' as create_date,
class_id,
time_type,
dt,
studying_student_count,
studying_cnt,
if(studying_student_count=0,0,studying_cnt*1.00000/studying_student_count) as studying_pct,
late_cnt,
if(studying_student_count=0,0,late_cnt*1.00000/studying_student_count) as late_pct,
leave_cnt,
if(studying_student_count=0,0,leave_cnt*1.00000/studying_student_count) as leave_pct,
studying_student_count-studying_cnt-leave_cnt-late_cnt as truant_cnt,
if(studying_student_count=0,0,(studying_student_count-studying_cnt-leave_cnt-late_cnt)*1.00000/studying_student_count) as truant_pct
from tmp1
)
select * from  tmp2;"


